package com.yystar.security.support.jdbc;

import java.io.Serializable;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.Id;
import javax.persistence.Table;
import javax.sql.DataSource;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.reflect.FieldUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

/**
 * Usage: This is a generic class for data access. Entity must have @Id
 * annotation on identity field.
 * 
 * @author pengzhanwang
 * 
 * @param <T>
 */
@Repository
public class GenericDao<T extends Serializable> {
	private final Logger log = LoggerFactory.getLogger(getClass());

	public final static String WHERE_IDS = "whereSb";
	public final static String WHERE_OBJECTS = "whereObject";
	private String tableName;

	@Autowired
	private DataSource dataSource;

	@Autowired
	private DataSource ccmsDataSource;

	/**
	 * Get default(bas's) data source
	 * 
	 * @return
	 */
	public JdbcTemplate getJdbcTemplate() {
		return new JdbcTemplate(dataSource);
	}

	/**
	 * Get ccms's data source
	 * 
	 * @return
	 */
	public JdbcTemplate getCcmsJdbcTemplate() {
		return new JdbcTemplate(ccmsDataSource);
	}

	public GenericDao() {
	}

	private Class<T> entityClass;

	/**
	 * Get generic class
	 * 
	 * @return Class<T>
	 */
	@SuppressWarnings("unchecked")
	protected Class<T> getEntityClass() {
		if (entityClass == null) {
			entityClass = (Class<T>) ((ParameterizedType) getClass()
					.getGenericSuperclass()).getActualTypeArguments()[0];
		}
		return entityClass;
	}

	/**
	 * Get table name of Entity
	 * 
	 * @return tableName
	 */
	protected String getTableName() {
		if (this.tableName == null) {
			Table table = getEntityClass().getAnnotation(Table.class);
			if (table == null) {
				log.error("Annotation @Table not found!");
			}
			this.tableName = table.name();
		}
		return this.tableName;
	}

	private String id;

	/**
	 * delete by id
	 */
	public int delete(Serializable... entityIds) {
		String sql = "delete from " + getTableName() + " where " + getId()
				+ " = ?";
		int excutCount = 0;
		for (Serializable entityId : entityIds) {
			getJdbcTemplate().update(sql, entityId);
			excutCount++;
		}

		return excutCount;
	}

	/**
	 * Get field name of id annotation
	 * 
	 * @return fieldName
	 */
	protected String getId() {
		String fieldName = StringUtils.EMPTY;
		if (this.id == null) {
			Field[] fields = getEntityClass().getDeclaredFields();
			for (Field field : fields) {
				if (isIdField(field)) {
					fieldName = field.getName();
					break;
				}
			}
			if (StringUtils.isEmpty(fieldName)) {
				log.error(getTableName() + " have not specific Id");
			}
		} else {
			return this.id;
		}

		return fieldName;
	}

	/**
	 * Is this a id field
	 * 
	 * @param field
	 * @return true or false
	 */
	private boolean isIdField(Field field) {
		Annotation[] annotations = field.getAnnotations();
		for (Annotation annotation : annotations) {
			if (annotation instanceof Id) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Find a object by Id Find <T>
	 */
	public T find(Serializable entityId) {
		String sql = "select * from " + getTableName() + " where " + getId()
				+ " = ?";
		T t = null;
		try {
			t =  getJdbcTemplate().queryForObject(sql,
					new Object[] { entityId }, new BeanPropertyRowMapper<T>(getEntityClass()));
		} catch (EmptyResultDataAccessException e) {
			log.debug("empty result data");
		}
		return t;
	}

	/**
	 * save entity please note that: the table must be auto increment field.
	 * 
	 * @return
	 */
	public int saveAndReturnKey(T entity) {
		SimpleJdbcInsert insertActor = getSimpleJdbcInsert();
		insertActor.setTableName(getTableName());
		insertActor.setGeneratedKeyName(getId());
		return insertActor.executeAndReturnKey(
				new BeanPropertySqlParameterSource(entity)).intValue();
	};

	/**
	 * Get SimpleJdbcInsert instance
	 * 
	 * @return
	 */
	public SimpleJdbcInsert getSimpleJdbcInsert() {
		return new SimpleJdbcInsert(getJdbcTemplate());
	}

	/**
	 * Execute the insert using the values passed in.
	 * 
	 * @return the number of rows affected as returned by the JDBC driver
	 */
	public int save(T entity) {
		SimpleJdbcInsert insertActor = getSimpleJdbcInsert();
		insertActor.setTableName(getTableName());
		return insertActor.execute(new BeanPropertySqlParameterSource(entity));
	};

	/**
	 * Get field count which is not null.
	 * 
	 * @param fields
	 * @param entity
	 * @return
	 */
	private int getNotNullFieldSize(Field[] fields, T entity) {
		int size = 0;
		try {
			for (Field field : fields) {
				Object value = FieldUtils.readDeclaredField(entity,
						field.getName(), true);
				if (value != null) {
					size++;
				}
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return size;
	}

	/**
	 * update single entity Entity Id required update non-null field.
	 */
	public int update(T entity) {
		String id = getId();
		StringBuilder sql = new StringBuilder();
		sql.append("update ");
		sql.append(getTableName());
		sql.append(" set ");
		Field[] fields = entity.getClass().getDeclaredFields();
		Object[] params = new Object[getNotNullFieldSize(fields, entity)];
		int index = 0;
		try {
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				Object value = FieldUtils.readDeclaredField(entity,
						field.getName(), true);
				// if value is null,then discard
				boolean isNotIdField = !id.equals(field.getName());
				if (isNotIdField && null != value) {
					sql.append(" ");
					sql.append(field.getName());
					sql.append(" = ? ");
					params[index++] = FieldUtils.readDeclaredField(entity,
							field.getName(), true);
					sql.append(",");
				}

			}
			sql.append("where" + id);
			sql = new StringBuilder(sql.toString().replace(",where", " where "));
			sql.append(" = ?");
			params[index] = FieldUtils.readDeclaredField(entity, id, true);
		} catch (IllegalAccessException e) {
			log.debug(e.getMessage());
			e.printStackTrace();
		}
		return update(sql.toString(), params);
	}

	/**
	 * update through sql with params
	 * 
	 * @param sql
	 * @param params
	 * @return result count.
	 */
	public int update(String sql, Object... params) {
		return getJdbcTemplate().update(sql, params);
	}

	/**
	 * find all of entity
	 */
	public List<T> findAll() {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from ").append(getTableName());
		return findBySQL(sql.toString());
	}

	/**
	 * find all by sql
	 */
	public List<T> findBySQL(String sql) {
		return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<T>(getEntityClass()));
	}

	/**
	 * find by sql with params
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<T> find(String sql, Object... params) {
		return getJdbcTemplate().query(sql.toString(), params,
				new BeanPropertyRowMapper<T>(getEntityClass()));
	}
	
	/**
	 * find by sql with params
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<T> find(JdbcTemplate template, String sql, Object... params) {
		return template.query(sql.toString(), params,
				new BeanPropertyRowMapper<T>(getEntityClass()));
	}

	/**
	 * Find Pager
	 * 
	 * @param pageIndex
	 * @param pageSize
	 * @param params
	 *            like name="jetty" you can put it in map
	 *            params.put("name","jetty");
	 * @param orders
	 * @return
	 */
	public Pager<T> findPager(int pageIndex, int pageSize,
			Map<String, Object> params, List<Order> orders) {
		String sql = "select * from " + getTableName();
		Object[] sqlParams = null;
		if (MapUtils.isNotEmpty(params)) {
			// log.error("Throw error when execute findPager method,params is empty!");
			// Append params to sql string.
			Set<String> pKeys = params.keySet();
			sqlParams = new Object[params.size()];
			sql += " where ";
			int i = 0;
			for (String key : pKeys) {
				sql += key + " like ? ";
				sqlParams[i] = "%" + params.get(key) + "%";
				// last parameter can't contact ','
				if (i != params.size() - 1) {
					sql += ",";
				}
				i++;
			}
		}

		// Append orders to sql string.
		if (CollectionUtils.isNotEmpty(orders)) {
			sql += " order by ";
			int j = 0;
			for (Order order : orders) {
				sql += order.getName() + " " + order.getSort();
				if (j != orders.size() - 1) {
					sql += ",";
				}
				j++;
			}
		}

		return findPager(sql, pageIndex, pageSize, sqlParams);
	}

	public Pager<T> findPager(String sql, int pageIndex, int pageSize,
			Object... sqlParams) {
		int start = (pageIndex - 1) * pageSize;
		int totalCount = getCount(sql, sqlParams);
		// Page count
		int pageSum = 1;
		if (totalCount % pageSize == 0) {
			pageSum = totalCount / pageSize;
		} else {
			pageSum = totalCount / pageSize + 1;
		}
		sql += " limit " + start + "," + pageSize;
		log.debug("execute findPager SQL : " + sql);
		List<T> list = null;
		if (ArrayUtils.isNotEmpty(sqlParams)) {
			list = find(sql, sqlParams);
		} else {
			list = findBySQL(sql);
		}
		return new Pager<T>(totalCount, pageIndex, pageSum, pageSize, list);
	}

	/**
	 * Find a pager
	 * 
	 * @param pageIndex
	 * @param pageSize
	 * @return
	 */
	public Pager<T> findPager(int pageIndex, int pageSize) {
		int totalCount = getCount();
		int start = (pageIndex - 1) * pageSize;
		// Page count
		int pageSum = 1;
		if (totalCount % pageSize == 0) {
			pageSum = totalCount / pageSize;
		} else {
			pageSum = totalCount / pageSize + 1;
		}

		String sql = "select * from " + getTableName() + " limit " + start
				+ "," + pageSize;
		List<T> list = findBySQL(sql);
		log.debug(sql);
		return new Pager<T>(totalCount, pageIndex, pageSum, pageSize, list);
	}

	/**
	 * Get all count of entity
	 */
	public int getCount() {
		String sql = "select count(1) from " + getTableName();
		return getJdbcTemplate().queryForObject(sql, Integer.class);
	}

	/**
	 * Get count of entity by condition
	 */
	public int getCount(String sql, Object... args) {
		String s = sql.replace("*", "count(1)");
		return getJdbcTemplate().queryForObject(s,args, Integer.class);
	}
	
	/**
	 * Get the total number of data using certain SQL
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public Integer getTotalCount(List<Where> wheres) throws Exception {
		/** Generate query condition and objects */
		Map<String, Object> whereMap = this.generateWhere(wheres);
		StringBuffer whereSb = (StringBuffer) whereMap.get(WHERE_IDS);
		Object[] objects = whereMap.get(WHERE_OBJECTS) == null ? null : (Object[]) whereMap.get(WHERE_OBJECTS);

		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("SELECT COUNT(1) FROM ");
		sqlSb.append(this.getTableName());
		sqlSb.append(whereSb);
		traceSql(sqlSb.toString(), objects);
		return getJdbcTemplate().queryForInt(sqlSb.toString(), objects);
	}
	
	/**
	 * Get all the current sub class's object data
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<T> getAllDataList(List<Where> wheres, List<Order> orders) throws Exception {
		/** Generate query condition and objects */
		Map<String, Object> whereMap = this.generateWhere(wheres);
		StringBuffer whereSb = (StringBuffer) whereMap.get(WHERE_IDS);
		Object[] objects = whereMap.get(WHERE_OBJECTS) == null ? null : (Object[]) whereMap.get(WHERE_OBJECTS);
		
		/** Generate sort condition for query data */
		StringBuffer orderSb = this.generateOrder(orders);

		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("SELECT * FROM ");
		sqlSb.append(this.getTableName());
		sqlSb.append(whereSb);
		sqlSb.append(orderSb);
		traceSql(sqlSb.toString(), objects);

		return (List<T>) getJdbcTemplate().query(sqlSb.toString(), objects, new BeanPropertyRowMapper(getEntityClass()));
	}
	
	/**
	 * Get the total number of data using sql template
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public Integer getTotalCount(JdbcTemplate template, String sql, Object... objects) throws Exception {
		return template.queryForInt(sql, objects);
	}
	
	/**
	 * Get all data list according to query condition using sql template
	 * 
	 * @param sql
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<T> getDataList(JdbcTemplate template, final String sql, Object... objects) throws Exception {
		return (List<T>) template.query(sql, objects, new BeanPropertyRowMapper(getEntityClass()));
	}

	/**
	 * 
	 * @param beanClass
	 * @param pageSize
	 * @param pageNo
	 * @param fields
	 * @param wheres
	 * @param orders
	 * @return
	 * @return
	 * @throws Exception
	 */
	public Pager<T> getListForPage(JdbcTemplate template, int pageSize, int pageNo, List<String> fields, List<Where> wheres, List<Order> orders)
			throws Exception {
		String tbName = getTableName();

		/** Generate query condition and objects */
		Map<String, Object> whereMap = this.generateWhere(wheres);
		StringBuffer whereSb = (StringBuffer) whereMap.get(WHERE_IDS);
		Object[] objects = whereMap.get(WHERE_OBJECTS) == null ? null : (Object[]) whereMap.get(WHERE_OBJECTS);

		/** Retrieve total count according query condition */
		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("SELECT COUNT(1) FROM " + tbName);
		sqlSb.append(whereSb);
		int totalCount = getTotalCount(template, sqlSb.toString(), objects);
		if (totalCount == 0) {// If no data
			return new Pager<T>(totalCount, pageSize);
		}

		/** Calculate total pages */
		int pageSum = 1;
		if (totalCount % pageSize == 0) {
			pageSum = totalCount / pageSize;
		} else {
			pageSum = totalCount / pageSize + 1;
		}

		/** Generate query field */
		StringBuffer fieldSb = this.generateField(fields);

		/** Generate sort condition for query data */
		StringBuffer orderSb = this.generateOrder(orders);

		/** Calculate the offset and list number */
		int offset = pageSize * (pageNo - 1);
		int listCount = pageSize;
		if (offset + pageSize > totalCount)
			listCount = totalCount - offset;
		if (listCount < 0) {
			offset = 0;
			listCount = pageSize;
		}
		/** Retrieve all data */
		sqlSb = new StringBuilder();
		sqlSb.append("SELECT ");
		sqlSb.append(fieldSb);
		sqlSb.append(" FROM ");
		sqlSb.append(tbName);
		sqlSb.append(whereSb);
		sqlSb.append(orderSb);
		if (pageNo > 0) {
			sqlSb.append(" LIMIT ");
			sqlSb.append(offset + ",");
			sqlSb.append(listCount);
		}
		List<T> resultList = this.getDataList(template, sqlSb.toString(), objects);
		return new Pager<T>(totalCount, pageNo, pageSum, pageSize, resultList);
	}

	/**
	 * The common update function using object list
	 * 
	 * @param updates
	 * @param wheres
	 * @return
	 */
	public int update(List<Update> updates, List<Where> wheres) throws Exception {
		if (updates == null || updates.isEmpty())
			return 0;

		int updateLen = updates.size();
		int whereLen = wheres.size();
		Object[] objects = new Object[updateLen + whereLen];
		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("UPDATE ");
		sqlSb.append(getTableName());
		sqlSb.append(" SET ");

		/** For each update information */
		for (int i = 0; i < updateLen; i++) {
			Update update = updates.get(i);
			if (i > 0)
				sqlSb.append(", ");
			sqlSb.append(update.getName() + " = ?");

			objects[i] = update.getValue();
		}

		/** For each update condition */
		if (wheres != null && !wheres.isEmpty()) {
			sqlSb.append(" WHERE ");
			for (int j = 0; j < whereLen; j++) {
				Where where = wheres.get(j);
				if (j > 0)
					sqlSb.append(" " + where.getRelation() + " ");
				sqlSb.append(where.getName());
				sqlSb.append(" " + where.getBind().toUpperCase() + " ?");

				objects[updateLen + j] = where.getValue();
			}
		}

		return update(sqlSb.toString(), objects);
	}
	

	/**
	 * Generate query field
	 * 
	 * @param fields
	 * @return
	 */
	public StringBuffer generateField(List<String> fields) {
		StringBuffer fieldSb = new StringBuffer("");
		if (fields != null && !fields.isEmpty()) {
			for (int i = 0; i < fields.size(); i++) {
				if (i > 0)
					fieldSb.append(",");
				fieldSb.append(fields.get(i));
			}
		} else
			fieldSb = new StringBuffer("*");

		return fieldSb;
	}

	/**
	 * Generate query condition
	 * 
	 * @param wheres
	 * @return
	 */
	public Map<String, Object> generateWhere(List<Where> wheres) {
		Map<String, Object> map = new HashMap<String, Object>();

		/** Generate query condition and objects */
		StringBuffer whereSb = new StringBuffer("");
		Object[] objects = null;
		if (wheres != null && !wheres.isEmpty()) {
			objects = new Object[wheres.size()];

			whereSb.append(" WHERE ");
			for (int i = 0; i < wheres.size(); i++) {
				Where where = wheres.get(i);
				if (i > 0)
					whereSb.append(" " + where.getRelation() + " ");
				whereSb.append(where.getName());
				whereSb.append(" " + where.getBind().toUpperCase() + " ?");

				objects[i] = where.getValue();
			}
		}
		map.put(WHERE_IDS, whereSb);
		map.put(WHERE_OBJECTS, objects);
		return map;
	}

	/**
	 * Generate order condition
	 * 
	 * @param orders
	 * @return
	 */
	public StringBuffer generateOrder(List<Order> orders) {
		StringBuffer orderSb = new StringBuffer("");
		if (orders != null && !orders.isEmpty()) {
			orderSb.append(" ORDER BY ");
			for (int i = 0; i < orders.size(); i++) {
				Order order = orders.get(i);
				if (i > 0)
					orderSb.append(",");
				orderSb.append(order.getName() + " " + order.getSort());
			}
		}
		return orderSb;
	}
	
	public void traceSql(String sql, Object[] objects) {
		StringBuffer sb = new StringBuffer();
		sb.append("SQL=[" + sql + "].");
		if (objects != null) {
			sb.append(" FIELDS=[");
			for (Object object : objects) {
				sb.append(object + ",");
			}
			sb.append("]");
		}
		log.debug(sb.toString());
	}
}
